package org.projetjava.metier.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.projetjava.metier.model.*;

public class NewsDao {
	
	private Connection con;
	
	private PreparedStatement prepStatAjoutNews = null;
	private PreparedStatement prepStatUpdateNews = null;
	private PreparedStatement prepStatIdNews = null;
	private PreparedStatement prepStatListNews = null;
	private PreparedStatement prepStatListNewsLimit = null;
	private PreparedStatement prepStatListSupprNewsNom = null;
	private PreparedStatement prepStatListSupprNewsId = null;
	private PreparedStatement prepStatListNewshyperText = null;
	private PreparedStatement prepStatHyperText = null;
	
	public NewsDao()
	{
		Bdd bdd = new Bdd();
		
		this.con = bdd.getConnection();
		
		bdd = null;
	}
	
	public void AjoutNews(String libelleNews, String Text_News)
	{
		try {
			if(this.prepStatAjoutNews == null)
			{
				this.prepStatAjoutNews = this.con.prepareStatement("INSERT INTO News( Libelle_News, Texte_Description_News) VALUES (?,?)");
			}
			
			this.prepStatAjoutNews.setString(1, libelleNews);
			this.prepStatAjoutNews.setString(2, Text_News);
			
			this.prepStatAjoutNews.executeUpdate();
		
		
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
	}
	
	public void UpdateNews(int IdNews, String libelleNews, String Text_News)
	{
		try {
			if(this.prepStatUpdateNews == null)
			{
				this.prepStatUpdateNews = this.con.prepareStatement("UPDATE News SET Libelle_News=?,Texte_Description_News=? WHERE ID_News=?");
			}
			
			this.prepStatUpdateNews.setString(1, libelleNews);
			this.prepStatUpdateNews.setString(2, Text_News);
			this.prepStatUpdateNews.setInt(3, IdNews);
			this.prepStatUpdateNews.executeUpdate();
		
		
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
	}
	
	public int IdNewsParNom(String libelleNews)
	{
		int idNews = 0;
		try {
			if(this.prepStatIdNews == null)
			{
				this.prepStatIdNews = this.con.prepareStatement("SELECT ID_News FROM News WHERE Libelle_News=?");
			}
			
			this.prepStatIdNews.setString(1, libelleNews);
			ResultSet result = this.prepStatIdNews.executeQuery();
			
			while(result.next()){          
				if(result.first())
				{
					idNews = (int) result.getObject(1);
				}
			}
		
		
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
		return idNews;
	}
	
	public List<News> ListNews()
	{
		List<News> listResultat = new ArrayList<>();
		
		List<String> listHyperText = new ArrayList<>();
		
		try {
			if(this.prepStatListNews == null)
			{
				this.prepStatListNews = this.con.prepareStatement("SELECT Libelle_News,Texte_Description_News,ID_News FROM News ORDER BY ID_News DESC LIMIT 0,?");
			}
			ResultSet result = this.prepStatListNews.executeQuery();
			
			while(result.next()){          
				News news = new News();

				news.setLibelleNews(result.getObject(1).toString());
				news.setDescriptionNews(result.getObject(2).toString());
				
				if(this.prepStatHyperText == null)
				{
					this.prepStatHyperText = this.con.prepareStatement("SELECT Lien_HyperText FROM LienHyperText WHERE ID_News = ?");
				}
				this.prepStatHyperText.setInt(1, (int) result.getObject(3));
				ResultSet resultHyperText = this.prepStatHyperText.executeQuery();
				while(resultHyperText.next()){
					listHyperText.add(result.getObject(1).toString());
				}
				news.setLienHyperText(listHyperText);
				listResultat.add(news);
			}
		
		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
		return listResultat;
	}
	
	public List<News> ListNewsLimit(int nombreResultat)
	{
		List<News> listResultat = new ArrayList<>();
		
		List<String> listHyperText = new ArrayList<>();
		
		try {
			if(this.prepStatListNewsLimit == null)
			{
				this.prepStatListNewsLimit = this.con.prepareStatement("SELECT Libelle_News,Texte_Description_News,ID_News FROM News ORDER BY ID_News DESC LIMIT 0,?");
			}
			this.prepStatListNewsLimit.setInt(1, nombreResultat);
			ResultSet result = this.prepStatListNewsLimit.executeQuery();
			
			while(result.next()){    
				
				News news = new News();
				
				news.setLibelleNews(result.getObject(1).toString());
				news.setDescriptionNews(result.getObject(2).toString());

				if(this.prepStatHyperText == null)
				{
					this.prepStatHyperText = this.con.prepareStatement("SELECT Lien_HyperText FROM LienHyperText WHERE ID_News = ?");
				}
				this.prepStatHyperText.setInt(1, (int) result.getObject(3));
				ResultSet resultHyperText = this.prepStatHyperText.executeQuery();
				while(resultHyperText.next()){
					listHyperText.add(result.getObject(1).toString());
				}
				news.setLienHyperText(listHyperText);
				
				listResultat.add(news);
				
			}

		} catch (SQLException e) { 
			e.printStackTrace();
	    }
		
		return listResultat;
	}

	//Attention il faut supprimer sur toutes les autres table se qui icorpore l'id
	public void SupprNewsParNom(String libelleNews)
	{
		try {
			//prepStatListNewshyperText 
			if(this.prepStatListNewshyperText == null)
			{
				this.prepStatListNewshyperText = this.con.prepareStatement("DELETE FROM LienHyperText WHERE ID_News = ?");
			}
			this.prepStatListNewshyperText.setInt(1, IdNewsParNom(libelleNews));
			this.prepStatListNewshyperText.executeUpdate();
			
			
			if(this.prepStatListSupprNewsNom == null)
			{
				this.prepStatListSupprNewsNom = this.con.prepareStatement("DELETE FROM News WHERE Libelle_News=?");
			}
			this.prepStatListSupprNewsNom.setString(1, libelleNews);
			this.prepStatListSupprNewsNom.executeUpdate();

		} catch (SQLException e) { 
			e.printStackTrace();
	    }
	}
	
	public void SupprNewsParID(int idNews)
	{
		try {
			//prepStatListNewshyperText 
			if(this.prepStatListNewshyperText == null)
			{
				this.prepStatListNewshyperText = this.con.prepareStatement("DELETE FROM LienHyperText WHERE ID_News = ?");
			}
			this.prepStatListNewshyperText.setInt(1, idNews);
			this.prepStatListNewshyperText.executeUpdate();
			
			if(this.prepStatListSupprNewsId == null)
			{
				this.prepStatListSupprNewsId = this.con.prepareStatement("DELETE FROM News WHERE ID_News=?");
			}
			this.prepStatListSupprNewsId.setInt(1, idNews);
			this.prepStatListSupprNewsId.executeUpdate();

		} catch (SQLException e) { 
			e.printStackTrace();
	    }
	}
	

}
